﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using ModelLayer;

namespace DBLayer
{
    public class SaleDBClient : ISaleDB
    {
        private static SqlCommand dbCmd;

        private IShowDB showDb = new ShowDBClient();
        private IPersonDB perDb = new PersonDBClient();

        public int InsertSale(Sale s)
        {
            int rc = -1;
            int personID = 0;
            if (s.Person.GetType().Equals(typeof(Person)) && s.Person.Id == 0 && !(s.Person.FName.Equals("Kontantsalg")))
            {
                perDb.InsertPerson(s.Person);
                personID = GetMaxPersonId();
            }
            else
            {
                personID = s.Person.Id;
            }

            string sql = "insert into Sale(totalprice, payed, paymentdate, showID, personID, seatsReserved) " + 
                "Values(" +
                "CAST(" + s.TotalPrice.ToString("0.00", CultureInfo.InvariantCulture) + " AS Decimal(12, 2))" + ", '" +
                s.Payed.ToString() + "', '" +
                s.PaymentDate.ToString() + "', " +
                s.Show.Id + ", " +
                personID + ", " +
                s.SeatsReserved + ")";

            rc = AccessDBSQLClient.ExecuteQuery(sql);

            return rc;
        }

        public Sale GetSale(int id)
        {
            string sql = "SELECT * FROM Sale WHERE id = " + id;
            
            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            Sale s;

            if (dbReader.Read())
            {
                s = CreateSaleObject(dbReader);
            }
            else
            {
                s = null;
            }
            dbReader.Close();
            dbCmd.Connection.Close();

            return s;
        }

        public int DeleteSale(int id)
        {
            string sql = "DELETE FROM Sale WHERE id = " + id;

            return AccessDBSQLClient.ExecuteQuery(sql);
        }

        public int UpdateSale(Sale s)
        {
            string sql = 
                "UPDATE Sale " + 
                "SET " + 
                "totalprice=" + "CAST(" + s.TotalPrice.ToString("0.00", CultureInfo.InvariantCulture) + " AS Decimal(12, 2)), " +
                "payed='" + s.Payed.ToString() + "', " +
                "paymentdate='" + s.PaymentDate.ToString() + "', " +
                "showID=" + s.Show.Id + ", " +
                "personID=" + s.Person.Id + ", " +
                "seatsReserved=" + s.SeatsReserved + "WHERE id = " + s.SaleID;

            return AccessDBSQLClient.ExecuteQuery(sql);
        }

        public List<Sale> GetUnpaidSales(string telephone)
        {
            List<Sale> salesList = new List<Sale>();
            string sql = "SELECT * FROM Sale WHERE payed='False'";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            while (dbReader.Read())
            {
                salesList.Add(CreateSaleObject(dbReader));
            }

            int i = 0;
            while (i < salesList.Count)
            {
                if (salesList[i].Person.Telephone.Equals(telephone))
                {
                    i++;
                }
                else
                {
                    salesList.RemoveAt(i);
                }
            }

            dbReader.Close();
            dbCmd.Connection.Close();

            return salesList;
        }

        public List<Sale> GetSalesByShow(Show s)
        {
            List<Sale> salesList = new List<Sale>();

            string sql = "SELECT * FROM Sale WHERE showID =" + s.Id;

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            while (dbReader.Read())
            {
                salesList.Add(CreateSaleObject(dbReader));
            }

            dbReader.Close();
            // dbCmd.Connection.Close();
            dbCmd.Connection.Close();

            return salesList;
        }

        private Sale CreateSaleObject(IDataReader dbReader)
        {
            Sale s = new Sale(
                    Convert.ToDecimal(dbReader["totalprice"].ToString()),
                    showDb.GetShow(Convert.ToInt32(dbReader["showID"].ToString())),
                    perDb.GetPerson(Convert.ToInt32(dbReader["personID"].ToString())),
                    Convert.ToBoolean(dbReader["payed"].ToString()),
                    Convert.ToDateTime(dbReader["paymentdate"].ToString()),
                    Convert.ToInt32(dbReader["seatsReserved"].ToString()));

            s.SaleID = Convert.ToInt32(dbReader["id"]);
            return s;
        }


        private int GetMaxPersonId()
        {
            int id = 0;

            string sql = "SELECT MAX(id) as id FROM Person";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            if (dbReader.Read())
            {
                id = Convert.ToInt32(dbReader["id"].ToString());
            }

            dbReader.Close();
            dbCmd.Connection.Close();

            return id;
        }

        
    }
}
